package com.zr.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.model.Worker;
import com.zr.util.JDBCUtil;

public class WorkerDao {
/**
 * 得到所有员工
 * @param start 分页位置
 * @param rows  分页记录数
 * @return
 */
	public List<Worker> getAllWorker(int start,int rows){
		//创建一个员工的集合
		List<Worker> workers=new ArrayList<Worker>();
		String sql="select * from worker limit ?,?";
		//链接数据库
		Connection con=JDBCUtil.getDBConnection();
		try {
			PreparedStatement pst= con.prepareStatement(sql);
		    pst.setInt(1, start);
		    pst.setInt(2, rows);
			ResultSet rs= pst.executeQuery();
		    while(rs.next()){
		    	//获取员工信息 并添加到员工集合中
                Worker worker= new Worker();
		    	worker.setWid(rs.getInt("wid"));
		    	worker.setWname(rs.getString("wname"));
		    	worker.setWage(rs.getInt("wage"));
		    	worker.setWsex(rs.getString("wsex"));
		    	worker.setWphone(rs.getString("wphone"));
		    	worker.setWadress(rs.getString("wadress"));
		    	worker.setDid(rs.getInt("did"));
		    	worker.setDname(rs.getString("dname"));
		    	worker.setWork_age(rs.getInt("work_age"));
		    	workers.add(worker);
		    }
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return workers;
	}
/**
 * 得到所有员工的数据
 * @return
 */
	public int getAllWorker(){
		//定义一个临时数据来接受员工的所有数据
		int wcount=0;
		String sql="select count(*) wcount from worker";
		Connection con= JDBCUtil.getDBConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql);
		    ResultSet rs= pst.executeQuery(); 
		    if(rs.next()){
		    	wcount=rs.getInt("wcount");
		    }
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.closedConnection(con);
		}
		return wcount;
	}
/**
 * 删除员工信息通过员工id
 * @param wid 传入员工id
 * @return
 */
	 public  int delWorkerByWid(int wid){
		 int w= 0;
		 String sql = "delete from worker where wid=?";
		 Connection con =JDBCUtil.getDBConnection();
			try {
				PreparedStatement pst= con.prepareStatement(sql);
				pst.setInt(1, wid);
				int i= pst.executeUpdate();
				if(i>0){
					w=1;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}  finally{
				JDBCUtil.closedConnection(con);
			}
		 return w;
	}
/**
 * 添加员工信息
 * @param wname
 * @param wage
 * @param wsex
 * @param wphone
 * @param wadress
 * @param did
 * @param dname
 * @param work_age
 * @return
 */
  public boolean addWorker(String wname,int wage,String wsex,String wphone,String wadress,int did,String dname,int work_age){
			boolean flag=false;
			String sql="insert into worker (wname,wage,wsex,wphone,wadress,did,dname,work_age) values(?,?,?,?,?,?,?,?)";
			Connection con=JDBCUtil.getDBConnection();
			PreparedStatement pst;
	  try {
				pst = con.prepareStatement(sql);
				pst.setString(1, wname);
				pst.setInt(2, wage);
				pst.setString(3, wsex);
				pst.setString(4, wphone);
				pst.setString(5, wadress);
				pst.setInt(6, did);
				pst.setString(7, dname);
				pst.setInt(8, work_age);
				int i=pst.executeUpdate();
		if(i>0){
					flag=true;
			}
		} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
	 }finally {
		JDBCUtil.closedConnection(con);
	}
			return flag;
   } 
/**
 *  修改员工信息的方法    
 * @param worker
 * @return
 */
     public int updateWoekerByWid(int wid,String wname,int wage,String wsex,String wphone,String wadress,int did,String dname,int work_age){
    	 int temp=0;
		 String sql="update worker set wname=?,wage=?,wsex=?,wphone=?,wadress=?,did=?,dname=?,work_age=? where wid=?";
		//连接数据库
		 Connection con=JDBCUtil.getDBConnection();
    	 try {
			PreparedStatement pst= con.prepareStatement(sql);
		     pst.setString(1, wname);
			 pst.setInt(2, wage);
			 pst.setString(3, wsex);
			 pst.setString(4, wphone);
			 pst.setString(5, wadress);
			 pst.setInt(6, did);
			 pst.setString(7, dname);
			 pst.setInt(8, work_age);
			 pst.setInt(9, wid);
    	    int i=pst.executeUpdate();
    	    if(i>0){
    	    	temp=1;
    	    }
    	 } catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.closedConnection(con);
		}
    	 return temp;
     }
     /**
      * 查询部门名称
      * @param did
      * @return
      */
     public List<Object> validataDepartment(int did){
    	 String dname="null";
    	 List<Object> listdname=new ArrayList<Object>();
		 String sql ="select dname from department where did=?";
		 Connection con=JDBCUtil.getDBConnection();
		 try {
			PreparedStatement pst=con.prepareStatement(sql);
			pst.setInt(1, did);
			ResultSet rs=pst.executeQuery();
		    if(rs.next()){
		    	listdname.add(rs.getString("dname"));
		    }else {
		    	listdname.add(dname);
			}
		 } catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.closedConnection(con);
		}
    	 return listdname;
     }
}
